package edu.uoc.tdp.pac4.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import edu.uoc.tdp.pac4.beans.Course;
import edu.uoc.tdp.pac4.beans.Usuari;

public class CourseManager {
	
	
	Connection con = null;
	PreparedStatement pst = null;
	ResultSet rs = null;

	DateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");

	
	public CourseManager() {
		// TODO Auto-generated constructor stub
	}
	

	private Connection getConnection() throws SQLException {
		Connection conn = null;
		conn = DiskManager.getInstance().getConnection();
		return conn;
	}

	
	public Course findCourseById(Integer id) {
		Course courseFilter = new Course();
		courseFilter.setCourseCode(id);
		List<Course> courses = findCourses(courseFilter);
		if(courses.size()!= 1){
			throw new RuntimeException("course not found");
		}
		return courses.get(0);
	}

	public List<Course> listCourse() {
		List<Course> courses = findCourses(null);
		return courses;
	}

	
	/**
	 * 
	 * @return
	 */
	public List<Course> findCourses(Course courseFilter) {
		System.out.println(courseFilter);
		List<Course> courses = new ArrayList<Course>();
		if(courseFilter == null)	
			courseFilter = new Course();
		
		
		try {//"SELECT * FROM curs WHERE "
			String querystring = ""
					+ "SELECT id, nom, minassistencia, credits, plaestudis, pladocent, bibliografia, datainactivitat "
					+ "FROM CURS WHERE '0'='0' ";
			
	querystring += " AND ( '0'='" + ((courseFilter.getCourseCode() == 0) ? 0 : 1) + "' OR  id = ? ) ";
	querystring += " AND ( '0'='" + ((courseFilter.getCourseName() == null) ? 0 : 1)+ "' OR  nom = ? ) ";
	querystring += " AND ( '0'='" + ((courseFilter.getReqAttendance() == 0) ? 0 : 1) + "' OR  minassistencia = ? ) ";
	querystring += " AND ( '0'='" + ((courseFilter.getCredits() == 0) ? 0 : 1) + "' OR  credits = ? ) ";
	querystring += " AND ( '0'='" + ((courseFilter.getPlaEstudis() == null) ? 0 : 1) + "' OR  plaestudis = ? ) ";
	querystring += " AND ( '0'='" + ((courseFilter.getPlaDocent() == null) ? 0 : 1) + "' OR  pladocent = ? ) ";
	querystring += " AND ( '0'='" + ((courseFilter.getBibliography() == null) ? 0 : 1) + "' OR  bibliografia = ? ) ";
	//querystring += " AND ( '0'='" + ((courseFilter.getInactivityDate() == null) ? 0 : 1) + "' OR  datainactivitat = ? ) ";
			
			System.out.println(querystring);
			
			con = getConnection();
			pst = con.prepareStatement(querystring);
			
			pst.setInt(1,courseFilter.getCourseCode());
			pst.setString(2,courseFilter.getCourseName());
			pst.setInt(3,courseFilter.getReqAttendance());
			pst.setInt(4,courseFilter.getCredits());			
			pst.setString(5, courseFilter.getPlaEstudis());
			pst.setString(6, courseFilter.getPlaDocent());
			pst.setString(7,courseFilter.getBibliography());

			
			rs = pst.executeQuery();
			while (rs.next()) {

				Course course= new Course();
				
				course.setCourseCode(rs.getInt("id"));
				course.setCourseName(rs.getString("nom"));
				course.setReqAttendance(rs.getInt("minassistencia"));
				course.setCredits(rs.getInt("credits"));
				course.setPlaEstudis(rs.getString("plaestudis"));
				course.setPlaDocent(rs.getString("pladocent"));
				course.setBibliography(rs.getString("bibliografia"));
				course.setInactivityDate(rs.getDate("datainactivitat"));
				
				courses.add(course);			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return courses;

	}

	public void createCourse(Course course) {

		try {
			con = getConnection();
			String pstQuery= "INSERT INTO " +
					" CURS( nom, minassistencia, credits, plaestudis, pladocent, bibliografia, datainactivitat )" 
					+ "VALUES(?,?,?,?,?,?,?)";
			pst=con.prepareStatement(pstQuery);
			//pst.setInt(1,course.getCourseCode());
			pst.setString(1,course.getCourseName());
			pst.setInt(2,course.getReqAttendance());
			pst.setInt(3,course.getCredits());			
			pst.setString(4, course.getPlaEstudis());
			pst.setString(5, course.getPlaDocent());
			pst.setString(6,course.getBibliography());
			pst.setDate(7,course.getInactivityDate()==null?null:new java.sql.Date(course.getInactivityDate().getTime()));
			
			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	public void updateCourse(Course course) {
		System.out.println(course);
		
		try {
			String querystring= "" +
					" UPDATE curs " +
					" SET nom=?, minassistencia=?, credits=?, plaestudis=?, pladocent=?, bibliografia=?, datainactivitat=? " +
					" WHERE id=?";

			con = getConnection();
			pst = con.prepareStatement(querystring);
			
			pst.setString(1,course.getCourseName());
			pst.setInt(2,course.getReqAttendance());
			pst.setInt(3,course.getCredits());			
			pst.setString(4, course.getPlaEstudis());
			pst.setString(5, course.getPlaDocent());
			pst.setString(6,course.getBibliography());
			pst.setDate(7,course.getInactivityDate()==null?null:new java.sql.Date(course.getInactivityDate().getTime()));
			pst.setInt(8,course.getCourseCode());
			
			
			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}			


	
	
		
		 
	 

	
	

}
